import numpy as np
import pandas as pd
from sqlalchemy import create_engine
#第一、二题
data = pd.read_excel(r"C:\Users\Administrator\Desktop\模块四作业数据\18级高一体测成绩汇总.xls",sheet_name=0)
data2 = pd.read_excel(r"C:\Users\Administrator\Desktop\模块四作业数据\体侧成绩评分表.xls",header=[0,1])


# con =  data2["男1000米跑"]["分数"]==95
# print(data2["男1000米跑"]["分数"][con].index)

# 3、评分标准中男1000米跑和女800米跑的成绩都是4‘10’‘这种形式，需要转化为float类型值
def deal_data(obj):
    if "'" in str(obj):
        cc = str(obj).replace("'",".")
        total_miao = float(cc)
    else:
        total_miao = float(obj)
    return total_miao

data["男1000米跑"] = data["男1000米跑"].map(deal_data)
# print(data)

# 4、其他所有数值类型的值，都要转换为float类型的值
data.iloc[::,[3,4,5,6,7,8,9,10]]= data.iloc[::,[3,4,5,6,7,8,9,10]].applymap(lambda x:float(x))
# print(data)

# 5、对体测成绩进行分数转换，跑步类（越小越好）；跳远、体前屈（越大越好
def del_data2(obj):
    return str(obj).replace("'",".").replace('"','')
new_data = data2["男1000米跑"].copy()
new_data["成绩"]=new_data["成绩"].apply(del_data2)
# data2["女800米跑"]["成绩"]=data2["女800米跑"]["成绩"].apply(del_data2)

def run_score(obj):
    # print(obj)
    for i in new_data["成绩"]:
        if float(i)>=obj:
            con = new_data["成绩"]==i
            indx2 = new_data["成绩"][con].index
            score=new_data["分数"][indx2[0]]
            return score

# data.insert(3,"男1000米跑分数",data["男1000米跑"].map(run_score))

def fei_score(obj):
    # dd = data2["男肺活量"]["成绩"].sort_index(ascending=False)
    dd = data2["男肺活量"]["成绩"]
    ls=[]
    max_v = dd.max()
    for i in dd:
        if float(i)<=obj:
            ls.append(i)
            con = data2["男肺活量"]["成绩"]==ls[-1]
            indx2 = data2["男肺活量"]["成绩"][con].index
            score=data2["男肺活量"]["分数"][indx2[0]]
            return score
        elif obj>max_v :
            return 100
        elif obj==0:
            return 0
# print(data["男肺活量"].map(fei_score))
# data.insert(8,"男肺活量分数",data["男肺活量"].map(fei_score))
# print(data)


def run50_score(obj):
    dd = data2["男50米跑"]["成绩"]
    max_v = dd.max()
    for i in dd:
        if float(i)>=obj:
            con = data2["男50米跑"]["成绩"]==i
            indx2 = data2["男50米跑"]["成绩"][con].index
            score=data2["男50米跑"]["分数"][indx2[0]]
            return score
        elif obj>max_v :
            return 100
        elif obj==0:
            return 0

# print(data["男50米跑"].map(run50_score))
# data.insert(4,"男50米跑分数",data["男50米跑"].map(run50_score))

def junp_score(obj):
    dd = data2["男跳远"]["成绩"]
    max_v = dd.max()
    for i in dd:
        if float(i)<=obj:
            con = data2["男跳远"]["成绩"]==i
            indx2 = data2["男跳远"]["成绩"][con].index
            score=data2["男跳远"]["分数"][indx2[0]]
            return score
        elif obj>max_v :
            return 100
        elif obj==0:
            return 0

# print(data["男跳远"].map(junp_score))
# data.insert(5,"男跳远分数",data["男跳远"].map(junp_score))

def qu_score(obj):
    dd = data2["男体前屈"]["成绩"]
    max_v = dd.max()
    for i in dd:
        if float(i)<=obj:
            con = data2["男体前屈"]["成绩"]==i
            indx2 = data2["男体前屈"]["成绩"][con].index
            score=data2["男体前屈"]["分数"][indx2[0]]
            return score
        elif obj>max_v :
            return 100
        elif obj==0:
            return 0

# print(data["男体前屈"].map(qu_score))
# data.insert(6,"男体前屈分数",data["男体前屈"].map(qu_score))

def body_score(obj):
    dd = data2["男引体"]["成绩"]
    max_v = dd.max()
    min_v = dd.min()
    for i in dd:
        if float(i)<=obj:
            con = data2["男引体"]["成绩"]==i
            indx2 = data2["男引体"]["成绩"][con].index
            score=data2["男引体"]["分数"][indx2[0]]
            return score
        elif obj>max_v :
            return 100
        elif obj<min_v:
            return 0

# print(data["男引体"].map(body_score))
# data.insert(5,"男引体",data["男体前屈"].map(qu_score))

if __name__ == '__main__':
    data.insert(3, "男1000米跑分数", data["男1000米跑"].map(run_score))
    data.insert(5, "男50米跑分数", data["男50米跑"].map(run50_score))
    data.insert(6, "男跳远分数", data["男跳远"].map(junp_score))
    data.insert(7, "男体前屈分数", data["男体前屈"].map(qu_score))
    data.insert(8, "男引体分数", data["男引体"].map(qu_score))
    data.insert(9, "男肺活量分数", data["男肺活量"].map(fei_score))
    print(data)

    data.to_excel('./score.xls',
    sheet_name = 'score',
    header = True,
    index = False)